<?php
class Mchapter extends CI_Model{
    public function __construct(){
        parent::__construct();
        $this->load->database();
    }

    public function getChaptersByBookUname($cate_uname, $book_uname,$volume_no){
        $this->db->select(' book.uname as b_uname, category.uname as c_uname, book.use_volume as book_use_volume, 
                            chapter.book_id as chapter_book_id, chapter.no as chapter_no, chapter.name as chapter_name, 
                            chapter.uname as chapter_uname, chapter.page_title as chapter_page_title, chapter.page_keyword as chapter_page_keyword, 
                            chapter.page_description as chapter_page_description, chapter.description as chapter_description, 
                            chapter.mob_description as chapter_mob_description
                          ');
        $this->db->from('book');
        $this->db->join('category_book' , 'book.id = category_book.book_id', 'inner');
        $this->db->join('category'      , 'category.id = category_book.category_id', 'inner');
        $this->db->join('chapter'       , 'chapter.book_id = book.id','inner');
        $this->db->where('category.uname'   , $cate_uname);
        $this->db->where('book.uname'       , $book_uname);
        $this->db->where('volume.no'       , $volume_no);
        $query = $this->db->get();

        return $query -> result_array();
    }

    public function getChaptersByBookIdByVolume($book_id, $number, $offset){

        $this->db->select(' volume.`id` as volume_id, volume.`no` as volume_no, volume.`name` as volume_name, 
                            chapter.no as chapter_no, chapter.name as chapter_name, chapter.uname as chapter_uname
                         ');
        $this->db->from('chapter');
        $this->db->join('volume', 'chapter.volume_no = volume.`no` AND chapter.book_id = volume.book_id','inner');
        $this->db->where('volume.book_id', $book_id);
        $this->db->order_by('volume.no', 'asc');
        $this->db->order_by('chapter_no', 'asc'); 
        $this->db->limit($number, $offset);
        $query = $this->db->get();
        return $query -> result_array();
    }

    public function getChaptersByBookId($book_id, $number, $offset){
        $this->db->select('chapter.no as chapter_no, chapter.name as chapter_name, chapter.uname as chapter_uname');
        $this->db->from('chapter');
        $this->db->where('chapter.book_id', $book_id);
        $this->db->order_by('chapter_no', 'asc'); 
        $this->db->limit($number, $offset);
        $query = $this->db->get();
        return $query -> result_array();
    }

    public function getChapterByNoByUname($cate_uname, $book_uname, $chapter_no, $chapter_uname){
        $this->db->select(' book.uname as b_uname, book.name as b_name, category.uname as c_uname, chapter.id as chapter_id,
                            chapter.book_id as chapter_book_id, chapter.no as chapter_no, chapter.name as chapter_name, 
                            chapter.page_title as chapter_page_title, chapter.page_keyword as chapter_page_keyword, 
                            chapter.page_description as chapter_page_description, chapter.description as chapter_description, 
                            chapter.mob_description as chapter_mob_description,chapter.uname as chapter_uname,
                            chapter.next_no as next_no, chapter.prev_no as prev_no,
                            volume.name as volume_name, volume.no as volume_no
                         ');
        $this->db->from('chapter');
        $this->db->join('book'       , 'chapter.book_id = book.id','inner');
        $this->db->join('category_book' , 'book.id = category_book.book_id','inner');
        $this->db->join('category'      , 'category.id = category_book.category_id','inner');
        $this->db->join('volume'      , 'volume.`no` = chapter.volume_no AND chapter.book_id = volume.book_id','left');
        $this->db->where('book.uname'       , $book_uname);
        $this->db->where('category.uname'   , $cate_uname);
        $this->db->where('chapter.no'    , $chapter_no);
        $this->db->where('chapter.uname'    , $chapter_uname);
        
        $query = $this->db->get();
        return $query -> row_array();
    } 

    public function getChapterByBookIdByNo($book_id, $id){
        $this->db->select('uname, no');
        $this->db->from('chapter');
        $this->db->where('book_id'   , $book_id);
        $this->db->where('id'       , $id);
        
        $query = $this->db->get();
        return $query -> row_array('');
    }
    
    public function getChapterByBookIdByFiveChapterLast($book_id){
        $this->db->select('id, name, book_id, volume_no, no, uname');
        $this->db->from('chapter');
        $this->db->where('book_id'   , $book_id);
        $this->db->order_by('volume_no', 'desc'); 
        $this->db->order_by('no', 'desc');
        $this->db->limit(5, 0);
        
        $query = $this->db->get();
        return $query -> result_array('');
    }

    public function getLastChapterByBookIdByVolume($book_id) {
        $sql = "SELECT chapter.`no` AS last_chapter_no,
                    chapter.`name` AS chapter_name,
                    chapter.`uname` AS chapter_uname,
                    volume.`no` AS volume_no
                FROM chapter
                INNER JOIN volume ON volume.`no` = chapter.volume_no AND chapter.book_id = volume.book_id
                WHERE chapter.volume_no = (SELECT MAX(`no`) FROM volume WHERE book_id = ?)
                AND chapter.book_id = ?
                ORDER BY chapter.`no` DESC
                LIMIT 0, 1";

        $query = $this->db->query($sql, array($book_id, $book_id));
        return $query -> row_array();
    }

    public function getLastChapterByBookId($book_id) {
        $sql = "SELECT chapter.`no` AS last_chapter_no,
                    chapter.`name` AS chapter_name,
                    chapter.`uname` AS chapter_uname
                FROM chapter
                WHERE chapter.`no` = (  SELECT MAX(chapter.`no`) 
                                        FROM chapter
                                        WHERE chapter.book_id = ?
                                        LIMIT 0, 1 ) 
                AND chapter.book_id = ?
                LIMIT 0, 1";

        $query = $this->db->query($sql, array($book_id, $book_id));
        return $query -> row_array();
    }

    public function countBookByBookId($book_id){
        $this->db->select('count(book_id) as total');
        $this->db->from('chapter');
        $this->db->where('book_id'   , $book_id);
        $query = $this->db->get();
        return $query->row('total');
    }
    
    public function countChapterByBookId($book_id){
        $this->db->select('count(`no`) as total');
        $this->db->from('chapter');
        $this->db->where('book_id'   , $book_id);        
        $query = $this->db->get();
        return $query->row('total');
    }
    
    public function increaseView($chapter_id){
        $sql = "UPDATE chapter SET view = view + 1 WHERE id = ?";

        $this->db->query($sql, array($chapter_id));
    }
    
    public function insertChapterError($param){
        $data = array(
            'id' => NULL,
            'chapterID' => $param['chapterID'],
        	'book_name' => $param['book_name'],
         	'chapter_no' => $param['chapter_no'],
            'date' => date("Y-m-d H:i:s"),
        	'url' => $param['url']
        );
        $this->db->insert('chapter_error', $data);
    }
}
?>